package com.cci.kangdao.dao;

import com.cci.kangdao.dao.model.BindESNWhiteT;
import com.cci.kangdao.dao.model.CustomerEngineRelT;
import com.cci.kangdao.dao.model.VipVehicleUser;
import com.cci.kangdao.dto.request.ButlerRequest;
import com.cci.kangdao.dto.response.CustomerEngineRelResponse;
import com.cci.kangdao.dto.response.GetCommercialCarListResponse;
import com.cci.kangdao.externalData.dto.request.SynchroLyhEsnRequest;
import com.cci.kangdao.model.ButlerInfo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.mybatis.spring.annotation.MapperScan;

import java.util.List;
import java.util.Map;

@MapperScan
public interface CustomerEngineRelDao {
	/**
	 * 用户添加设备信息接口
	 * @return
	 */
	public int addCustomerEngineRel(Map<String, Object> map);

	/**
	 * 用户esn列表
	 * @return
	 */
	List<CustomerEngineRelT> getEsnList(@Param("userId") Long userId);

	/**
	 * 用户修改设备信息接口
	 * @return
	 */
	public int updateCustomerEngineRel(Map<String, Object> map);

	/**
	 * 用户设备信息修改服务站编码接口
	 * @return
	 */
	public int updateBusinessNoByNoEsn(@Param("esn")String esn,@Param("userId")Long userId,@Param("businessNo")String businessNo);

	/**
	 * 用户设备信息修改服务站编码接口
	 * @return
	 */
	public int updateBusinessNoById(@Param("businessNo")String businessNo,@Param("id")Long id,@Param("updator")Long updator,@Param("updateTime")String updateTime);


	/**
	 * 用户删除设备信息接口
	 * @return
	 */
	public int deleteCustomerEngineRel(Map<String, Object> map);

	/**
	 * 用户删除设备信息接口
	 * @return
	 */
	public int deleteEngineRelByUserId(@Param("userId")Long userId,@Param("DeleteTime")String deleteTime);

	/**
	 * 山康云用户删除设备信息接口
	 * @return
	 */
	/**
	 * 山康云用户删除设备信息接口
	 * @return
	 */
	public int deleteEngineRelShanKangByUserId(@Param("userId")Long userId,@Param("DeleteTime")String deleteTime);


	/**
	 * 根据esn获取用户设备详细接口
	 * @param userId
	 * @return
	 */
	@Select("select top 1 * from CustomerEngineRel_T where Flag = 0 and UserId = #{userId} and ESN = #{esn}")
	CustomerEngineRelT getCustomerEngineRelByEsn(@Param("userId") Long userId,@Param("esn") String esn);

	/**
	 * 验证ESN是否重复
	 * @param map
	 * @return
	 */
	@Deprecated
	int getESN(Map<String,Object> map);

	@Select("select COUNT(*) from CustomerEngineRel_T cer,EngineMsg em where cer.EngineMsgId = em.id and cer.Flag=0 and cer.ESN = #{esn} and cer.UserId=#{userId}")
	int getRelationByUserIdAndESN(@Param("userId") Long userId, @Param("esn") String esn);

	int getRelationByUserIdAndESNList(@Param("userId") Long userId, @Param("esnList") List<String> esnList);

	List<CustomerEngineRelT> getEngineByUserIdAndESNList(@Param("userId") Long userId, @Param("esnList") List<String> esnList);

	List<CustomerEngineRelT> getRelationByUserIdAndESNListShankang(@Param("list") List<String> list);

	@Select("select top 1 * from CustomerEngineRel_T where Flag=0 and UserId=#{userId} order by CreateTime desc")
	CustomerEngineRelT getRelationByUserId(@Param("userId") Long userId);

	List<Map<String, Object>> getCustomerEngineRelList(Map<String, Object> map);

	List<ButlerInfo> getButlerServiceList(ButlerRequest request);

	int getCustomerEngineRelCount(@Param("userId") Long userId);

	List<Map<String, Object>> getHistoryCustomerEngineRel(Map<String, Object> map);

	List<Map<String, Object>> getDYCustomerEngineRelList(Map<String, Object> map);

	@Update("update EngineMsg set ShoppingDate = #{shoppingDate} where id = #{id}")
	void updateShoppingDate(Map<String, Object> map);

	@Select("select * from CustomerEngineRel_T where ID = #{id}")
	CustomerEngineRelT getCustomerEngineRelById(Long id);

	/**
	 * 根据用户信息获取管辖车
	 *
	 * @param userId
	 * @return
	 */
	@Select("select id, UserId, Phone, ESN, VIN, VIC, EngineMsgId, SMN, SMNName, OEMID, OEMName, Flag, Creator, CreateTime, Updator, UpdateTime, Deleter, DeleteTime, ServiceChannel, EquipmentType, DrivingLicensePhotoUrl, EquipmentPhotoUrl, NewVIN, EquipmentUse, ServiceStationId, BindAddMessage, BindAddStatus, CipReturnValue,BusinessNo,InviteCode,SalesChannel from CustomerEngineRel_T where UserId = #{userId} AND Flag = 0")
	List<CustomerEngineRelT> getListByUserId(Long userId);

	/**
	 * 根据用户信息获取管辖车
	 *
	 * @param userId
	 * @return
	 */
	@Select("select id, UserId, Phone, ESN, VIN, VIC, EngineMsgId, SMN, SMNName, OEMID, OEMName, Flag, Creator, CreateTime, Updator, UpdateTime, Deleter, DeleteTime, ServiceChannel, EquipmentType, DrivingLicensePhotoUrl, EquipmentPhotoUrl, NewVIN, EquipmentUse, ServiceStationId, BindAddMessage, BindAddStatus, CipReturnValue,BusinessNo from CustomerEngineRel_T where UserId = #{userId} and ServiceChannel = #{channel} AND Flag = 0")
	List<CustomerEngineRelT> getListByUserIdAndChannel(@Param("userId") Long userId,@Param("channel")String channel);

	/**
	 * 根据用户信息获取管辖车  方便添加使用
	 *
	 * @param userId
	 * @return
	 */
	@Select("select * from CustomerEngineRel_T where UserId = #{userId} AND Flag = 0")
	List<Map<String,Object>> getListMapByUserId(Long userId);
	/**
	 * 根据用户信息获取管辖车辆ESN信息
	 *
	 * @param userId
	 * @return
	 */
	@Select("SELECT ESN FROM CustomerEngineRel_T WHERE UserId = #{userId} AND Flag = 0")
	public List<String> getESNListByUserId(Long userId);

	/**
	 * 根据用户信息获取管辖的车辆数据信息
	 * @param userId
	 * @return
	 */
	@Select("SELECT em.* FROM EngineMsg em\n" +
			"INNER JOIN CustomerEngineRel_T cer ON em.ESN = cer.ESN\n" +
			"WHERE cer.UserId = #{userId} AND cer.Flag = 0")
	public List<Map<String,Object>> getESNModelListByUserId(Long userId);


	/**
	 * 根据用户信息获取管辖车辆ESN信息，及时已经删除也需要显示
	 * @param userId
	 * @return
	 */
	@Select("SELECT DISTINCT ESN FROM CustomerEngineRel_T WHERE UserId = #{userId}")
	public  List<String> getESNListByUserIdWithoutFlag(Long userId);

	/**
	 * 根据用户信息获取管辖的车辆数据信息，及时已经删除也需要显示
	 * @param userId
	 * @return
	 */
	@Select("SELECT em.* FROM EngineMsg em\n" +
			"INNER JOIN CustomerEngineRel_T cer ON em.ESN = cer.ESN\n" +
			"WHERE cer.UserId = #{userId}")
	public List<Map<String,Object>> getESNModelListByUserIdWithoutFlag(Long userId);

	@Select("select a.UserId as userId,a.ESN,b.Phone as phone,c.ClientId as clientId from CustomerEngineRel_T a\n" +
			"left join User_T b on a.UserId = b.ID\n" +
			"left join RelationShip_T c on b.UserName = c.UserName\n" +
            "where a.ESN in(#{esns}) and a.Flag = 0 and b.Flag = 0")
	List<Map<String,Object>> getUserInfoByEsns(@Param("esns") String esns);



	/**
	 * 新用户添加设备信息接口
	 * @return
	 */
	public int newAddCustomerEngineRel(Map<String, Object> map);
	/**
	 * 新用户修改设备信息接口
	 * @return
	 */
	public int newUpdateCustomerEngineRel(Map<String, Object> map);

	/**
	 * 新用户修改设备信息接口
	 * @return
	 */
	public int updateSynchroCustomerEngineRel(CustomerEngineRelT info);

	/**
	 * 新用户修改设备信息接口
	 * @return
	 */
	public int deleteSynchroCustomerEngineRel(CustomerEngineRelT info);

	/**
	 * 获取绑车esn白名单列表
	 * @return
	 */
	List<BindESNWhiteT> getBindESNWhiteList();

	int  updateByServiceChannel(@Param("serviceChannel") String serviceChannel,@Param("userID") Long userID);

	/**
	 * 根据esn获取最新设备详细
	 * @return
	 */
	CustomerEngineRelResponse getEngineByEsn(@Param("esn") String esn);



	Map<String,Object> getCustomerEngineById(@Param("customerEngineRelID") Long customerEngineRelID);

	Map<String,Object> getCustomerEngineByUserId(@Param("userId") Long userId,@Param("esn") String esn);

	//获取正常的未被车主取消的用户车辆信息（不含本人）
	@Select("select b.UserName as userName,b.Phone as phone,a.CreateTime as bindTime,a.IsCancelledAuth as isCancelledAuth,  \n" +
			"a.DeleteTime as unbindTime,a.ID as customerEngineRelId,ISNULL(c.CertifiedStatus,-1) as certifiedStatus\n" +
			"from CustomerEngineRel_T a\n" +
			"inner join User_T b on a.UserId  = b.ID\n" +
			"left join CertifiedOwnerApply_T c on a.CertifiedOwnerApplyId = c.ID  and c.Flag = 0\n" +
			"where (a.Flag = 0 and a.IsCancelledAuth = 0) and b.Flag = 0 and a.UserId != #{userId} " +
			"and a.ESN = (select top 1 ESN from CustomerEngineRel_T cert where ID = #{customerEngineRelId})" +
			"order by a.CreateTime desc")
	List<Map<String,Object>> getCustomerIsCancelledAuthByEsn(@Param("customerEngineRelId") Long customerEngineRelId,@Param("userId") Long userId);

	//获取被车主主动取消授权的用户车辆信息
	@Select("select b.UserName as userName,b.Phone as phone,a.CreateTime as bindTime,a.IsCancelledAuth as isCancelledAuth,  \n" +
			"a.DeleteTime as unbindTime,a.ID as customerEngineRelId,ISNULL(c.CertifiedStatus,-1) as certifiedStatus\n" +
            "from CustomerEngineRel_T a\n" +
            "inner join User_T b on a.UserId  = b.ID\n" +
            "left join CertifiedOwnerApply_T c on a.CertifiedOwnerApplyId = c.ID  and c.Flag = 0\n" +
            "where (a.Flag = -1 and a.IsCancelledAuth = 1) and b.Flag = 0 and a.UserId != #{userId} " +
            "and a.ESN = (select top 1 ESN from CustomerEngineRel_T cert where ID = #{customerEngineRelId})" +
            "order by a.DeleteTime desc")
    List<Map<String, Object>> getCustomerNotIsCancelledAuthByEsn(@Param("customerEngineRelId") Long customerEngineRelId, @Param("userId") Long userId);



	@Select("select a.ID ,a.VIN ,b.EsnApplicationType ,a.ESN ,a.IsImproveCarMsg ,a.OcrVehicleType ,a.OcrApplicationClass ,\n" +
			"a.FinalVehicleType ,a.FinalApplicationClass, a.FinalTotalMass ,a.FinalApprovedLoadCapacity , \n" +
			"a.FinalGabariteLength, a.FinalGabariteHeight,a.FinalGabariteWidth ,a.FinalAxlesNum  \n" +
			"from CustomerEngineRel_T a\n" +
			"inner join EngineMsg b on a.ESN  = b.ESN \n" +
			"where a.Flag = 0 and b.EsnApplicationType = 0 and a.UserId = #{userId}")
	List<GetCommercialCarListResponse> getCommercialCarList(@Param("userId") Long userId);
	@Select("select count(1) from CustomerEngineRel_T where Flag = 0 and UserId = #{userId}")
	int getCountByUserId(@Param("userId") long userId);

    List<Map<String, Object>> getCustomerEngineRelAndAmt(Map<String, Object> map);

	/**
	 * 新用户添加设备信息接口
	 * @return
	 */
	public int addVehicle(CustomerEngineRelT customerEngineRelT);
	int updateVehicle(CustomerEngineRelT customerEngineRelT);

	@Select("select count(1) from CustomerEngineRel_T where Flag = 0 and UserId != #{userId} and ESN = #{esn}")
	int getCountByUserIdAndEsn(@Param("userId") long userId,@Param("esn") String esn);

	@Select("select top 1 VIPType as vipType,ExpirationTime as expirationTime from VIP_VehicleUser_T where Flag = 0 and UserId = #{userId} and ESN = #{esn}")
	Map<String, Object> getVIPTypeByUserIdAndEsn(@Param("userId") long userId,@Param("esn") String esn);

	List<Map<String, Object>> getCustomerEngineByEsnOrFirst(@Param("esn") String esn, @Param("userId") long userId);

//select * from VIP_VehicleUser_T dt where UserId =#{userId} and ESN = #{esn} and Flag =0
	@Select("select * from VIP_VehicleUser_T dt where UserId =#{userId} and ESN = #{esn} and Flag =0")
	List<VipVehicleUser> getVipVehicleUser(@Param("userId") long userId, @Param("esn") String esn);



	/**
	 * 用户陕康云删除设备
	 * @return
	 */
	@Update("update CustomerEngineRel_T set flag=-1 where flag=0 and  UserId=#{userId}" +
			" and ESN =#{esn} and ServiceChannel= #{serviceChannel}")
	int delByUserIdAndEsnAndServiceChannel(@Param("userId") long userId,@Param("esn") String esn,@Param("serviceChannel") String serviceChannel);
    List<Map<String, Object>> getCustomerEngineRelByKeyWord(Map<String, Object> queryMap);

	List<String> selectRelSalesPlant(Map<String, Object> map);

	List<String> selectRrelBulletinTelAboPlatForm();

	List<String> selectRrelBulletinTelAboEmissionLevel();

	List<SynchroLyhEsnRequest> getCustomerESNListByLyh(@Param("userId") Long userId);

	int addSkyVehiclesFile(@Param("fileUrl") String fileUrl, @Param("time") String time);
}
